<%@ page language="java" %>
<%@ taglib uri="http://struts.apache.org/tags-bean" prefix="bean" %>
<%@ taglib uri="http://struts.apache.org/tags-html" prefix="html" %>
<%@ taglib uri="http://struts.apache.org/tags-logic" prefix="logic" %>

<html:html>
	<head>
		<title>Applicant</title>
	</head>
<body>
<%-- Import the java.sql package --%>
            <%@ page import="java.sql.*"%>
            <%-- -------- Open Connection Code -------- --%>
            <%  
            Connection conn = null;
            PreparedStatement pstmt = null;
            ResultSet rs = null;
            Statement stmt = null;
            
           try{
                // Registering Postgresql JDBC driver with the DriverManager
                Class.forName("org.postgresql.Driver");

                // Open a connection to the database using DriverManager
                conn = DriverManager.getConnection(
                		"jdbc:postgresql://localhost:5433/GradApp?" +
                        "user=postgres&password=20880130");
                 
                String applicant = request.getParameter("Applicant");
                int appID = Integer.parseInt(applicant);
                
                String QuaryString = "SELECT firstname,middle,lastname,C1.name AS citizenship,C2.name AS residence, "+
                "street,city,state,zipcode,areacode,number,telephonecode,Specialization.name AS specialization "+
                "FROM  Students,Countries C1,Countries C2, Specialization "+
                "WHERE Students.id = ? AND Students.citizenship = C1.id AND Students.residence = C2.id "+
                "AND Students.specialization = Specialization.id";
				pstmt = conn.prepareStatement(QuaryString);
				pstmt.setInt(1,appID);
				rs = pstmt.executeQuery();
				rs.next();
           
      %>
      Applicant's Basic Information<p>
      <table border = "1">
      <tr>
      <th>Name</th>
      <th>Citizenship	</th>
      <th>Residence		</th>
      <th>Address		</th>
      <th>Telephone 	</th>
      <th>Specialization </th>
      </tr> 
      <tr>
      <td> <%=rs.getString("firstname")+" "+ rs.getString("middle")+" "+ rs.getString("lastname")%> </td>
      <td><%=rs.getString("citizenship") %> </td>
      <td><%=rs.getString("residence")%> </td>
      <td><%=rs.getString("street")+" "+rs.getString("city")+" "+rs.getString("state")+" "
      		+rs.getString("zipcode")%> </td>
      <td> <%=rs.getString("telephonecode")+" "+rs.getString("areacode")+" "+rs.getString("number") %></td>
      <td> <%=rs.getString("specialization")%></td>
       
      </tr>    
      </table>   
      <BR> <BR>
      
      
      <%
      QuaryString = "SELECT Universities.name AS university, Discipline.name AS discipline, "+
      " month,year,gpa,titleofdegree "+ 
      "FROM  Universities,Discipline, Degrees "+
      "WHERE Degrees.students = ? AND Degrees.university =Universities.id AND Degrees.discipline=Discipline.id";
      
		pstmt = conn.prepareStatement(QuaryString);
		pstmt.setInt(1,appID);
		rs = pstmt.executeQuery();
		
      %>

      Applicant's Degree Information<p>
      <table border="1">
	      <tr>
	      <th>University</th>
	      <th>Discipline  </th>
	      <th> Graduation Date </th>
	      <th> GPA </th>
	      <th> Title of Degree </th>
	      </tr>
	      
	      <%while(rs.next()){%>
	      <tr>
	      <td> <%=rs.getString("university")%> </td>
	      <td> <%=rs.getString("discipline") %> </td>
	      <td> <%=rs.getInt("month")+" "+rs.getInt("year")%> </td>
	      <td> <%=rs.getFloat("gpa") %></td>
	      <td> <%=rs.getString("titleofdegree")%></td>
	     
	      </tr>
	      <%} %>
	      
      </table>
      
      <%     
           } catch (SQLException e) {

               // Wrap the SQL exception in a runtime exception to propagate
               // it upwards
               throw new RuntimeException(e);
           }
           finally {
               // Release resources in a finally block in reverse-order of
               // their creation

               if (rs != null) {
                   try {
                       rs.close();
                   } catch (SQLException e) { } // Ignore
                   rs = null;
               }
               if (pstmt != null) {
                   try {
                       pstmt.close();
                   } catch (SQLException e) { } // Ignore
                   pstmt = null;
               }
               if (conn != null) {
                   try {
                       conn.close();
                   } catch (SQLException e) { } // Ignore
                   conn = null;
               }
           } 
   %>      
</body>
</html:html>